package com.william.jdbcplus.core.jdbc.impl;

import com.alibaba.fastjson.JSONArray;
import com.william.jdbcplus.core.dialect.pagination.PaginationFactory;
import com.william.jdbcplus.core.jdbc.handle.impl.JsonHandler;
import com.william.jdbcplus.core.jdbc.handle.impl.ListHandler;
import com.william.jdbcplus.core.jdbc.handle.impl.MapHandler;
import com.william.jdbcplus.core.parser.SqlInfo;
import com.william.jdbcplus.core.toolkit.MapTransformUtils;
import com.william.jdbcplus.core.toolkit.SqlParserUtils;
import com.william.jdbcplus.core.util.JdbcUtil;
import com.william.jdbcplus.common.page.IPage;
import com.william.jdbcplus.common.page.Page;
import com.william.jdbcplus.core.jdbc.IBaseJdbc;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 执行sql脚本封装
 *
 * @author niliwei
 */
public class BaseJdbc implements IBaseJdbc {

    private static BaseJdbc instance = new BaseJdbc();

    private BaseJdbc() {
    }

    public static BaseJdbc getInstance() {
        return instance;
    }

    @Override
    public JSONArray queryToJsonArray(String sql, DataSource ds) {
        JSONArray jsonArray = null;
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = ds.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            jsonArray = JsonHandler.getInstance().handle(resultSet);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != resultSet) {
                    resultSet.close();
                }
                if (null != statement) {
                    statement.close();
                }
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }

        return jsonArray;
    }

    @Override
    public List<Map<String, Object>> queryToMapList(String sql, DataSource ds) {
        List<Map<String, Object>> resultList = new ArrayList<>();
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = ds.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            resultList = ListHandler.getInstance().handle(resultSet);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != resultSet) {
                    resultSet.close();
                }
                if (null != statement) {
                    statement.close();
                }
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }

        return resultList;
    }

    @Override
    public IPage<Map<String, Object>> queryMapPage(IPage<?> page, String sql, DataSource ds) {
        SqlInfo sqlInfo = SqlParserUtils.getOptimizeCountSql(sql);
        String sqlCount = sqlInfo.getSql();
        String sqlPage = PaginationFactory.getPaginationSql(page, sql, JdbcUtil.getDbType(ds));
        List<Map<String, Object>> listMap = queryToMapList(sqlPage, ds);
        int tatal = queryCount(sqlCount, ds);
        Page<Map<String, Object>> result = new Page<Map<String, Object>>(page.getCurrent(), page.getSize());
        result.setRecords(listMap);
        result.setTotal(tatal);
        return result;
    }

    @Override
    public Map<String, Object> queryToMap(String sql, DataSource ds) {
        Map<String, Object> resultMap = new HashMap<>();
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = ds.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            resultMap = MapHandler.getInstance().handle(resultSet);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != resultSet) {
                    resultSet.close();
                }
                if (null != statement) {
                    statement.close();
                }
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }

        return resultMap;
    }

    @Override
    public <T> T queryEntity(String sql, DataSource ds, Class<T> clazz) {
        Map<String, Object> map = queryToMap(sql, ds);
        T result = MapTransformUtils.mapToEntity(map, clazz);
        return result;
    }

    @Override
    public <T> List<T> queryEntityList(String sql, DataSource ds, Class<T> clazz) {
        List<Map<String, Object>> list = queryToMapList(sql, ds);
        List<T> result = MapTransformUtils.mapToEntityList(list, clazz);
        return result;
    }

    @Override
    public <T> IPage<T> queryEntityPage(IPage<?> page, String sql, DataSource ds, Class<T> clazz) {
        SqlInfo sqlInfo = SqlParserUtils.getOptimizeCountSql(sql);
        String sqlCount = sqlInfo.getSql();
        String sqlPage = PaginationFactory.getPaginationSql(page, sql, JdbcUtil.getDbType(ds));
        List<Map<String, Object>> listMap = queryToMapList(sqlPage, ds);
        List<T> listEntity = MapTransformUtils.mapToEntityList(listMap, clazz);
        int tatal = queryCount(sqlCount, ds);
        Page<T> result = new Page<>(page.getCurrent(), page.getSize());
        result.setRecords(listEntity);
        result.setTotal(tatal);
        return result;
    }

    @Override
    public Integer queryCount(String sql, DataSource ds) {
        String json = "";
        Map<String, Object> map = queryToMap(sql, ds);
        for (Map.Entry<String, Object> entry : map.entrySet()) {
            json = entry.getValue() == null ? "0" : entry.getValue().toString();
        }
        return Integer.parseInt(json);
    }

    @Override
    public int exec(String sql, DataSource ds) {
        int result = -1;
        Connection connection = null;
        Statement statement = null;
        try {
            connection = ds.getConnection();
            statement = connection.createStatement();
            result = statement.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != statement) {
                    statement.close();
                }
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }

        return result;
    }

    @Override
    public boolean execBatch(List<String> sqlList, DataSource ds) {
        boolean flag = true;
        Connection connection = null;
        Statement statement = null;

        try {
            connection = ds.getConnection();
            statement = connection.createStatement();
            connection.setAutoCommit(false);//取消自动提交
            for (String sql : sqlList) {
                statement.addBatch(sql);
            }
            //执行批处理语句
            statement.executeBatch();
            //如果没有异常，则执行此段代码
            //提交事务，真正向数据库中提交数据
            connection.commit();
            flag = true;
        } catch (Exception e) {
            //将数据回滚
            try {
                connection.rollback();
                flag = false;
            } catch (Exception e1) {
                flag = false;
            }
        } finally {
            //关闭
            try {
                if (null != statement) {
                    statement.close();
                }
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException ex) {
                flag = false;
            }
        }

        return flag;
    }
}
